var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery } = require('./server_util.js')

const log = require('../../../logger.js');
const db = require('../../../db/Redisdb.js');
var logger = require('../../../logger.js').logger;
const sdk = require('@baiducloud/sdk');


//物料档案导入
router.post('/wlda_import', async function (req, res) {
   var data = req.body
   var db = new Msdb()
   let errorRows = []
   let importSql=``
   for (var i = 0; i < data.length; i++) {
      let ifexist = await checkbysql(`select * from julongSRM_wlda where wlbm='${data[i].wlbm}'`)
      if (!ifexist) {
         importSql+=`insert into julongSRM_wlda (id,create_time,create_by,wlbm,wlmc,ggxh,lbbm,lbmc,dw,sfqy)
      values(NEXT VALUE FOR SEQ_ID,getdate(),'admin','${data[i].wlbm}','${data[i].wlmc}','${data[i].ggxh}','${data[i].lbbm}'
      ,'${data[i].lbmc}','${data[i].dw}','已启用')`
      }
      else{
         errorRows.push({rowNumber:i+2,wlbm:data[i].wlbm})
      }
   }
   await db.update(importSql)

   let me_sql = `
   merge into julongsrm_wlda a 
   using julongsrm_wlfl b
   on a.lbbm = b.flbm
   when matched then update set
   lbmc = '('+b.flbm+')'+b.flmc;`
   await db.update(me_sql)
  
   res_util(res, {errorRows}, true, "导入完成")
})


//修改物料分类
router.post('/edit_wlfl', async function (req, res) {
   var { flbm, flmc } = req.body
   console.log("当前的分类编码及分类名称为：",flbm, flmc)
   var db = new Msdb()
   //更新表wlda中的lbmc lbmc =  `(flbm)flmc`
   await db.update(`update julongSRM_wlda set lbmc='(${flbm})${flmc}' where lbbm = '${flbm}'`)
   
   var rtdata = await db.update(`update julongSRM_wlfl set flmc='${flmc}' where flbm='${flbm}'`)
   // 更新子分类的上级分类名称
   await db.update(`update julongSRM_wlfl set sjflmc='${flmc}' where sjflbm='${flbm}'`)
   // 更新物料档案中的分类名称
   res_util(res, rtdata, true, "修改成功")
})


//查询文档分类
router.post('/queryFileFlList', async function (req, res) {
   var db = new Msdb();
   var querytensql = `select * from  	julongSRM_wdfl   where  jjfl='1'`
   var rtdata = await db.query(querytensql);

   var querytensql2 = `select * from  	julongSRM_wdfl   where  jjfl='2'`
   var rtdata2 = await db.query(querytensql2);

   var result = {
      yjcdList: rtdata.recordsets[0],
      ejcdList: rtdata2.recordsets[0]
   }

   res.send(JSON.stringify(result));

});
//更新历史单据物料名称
router.post('/update_history_wlmc', async function (req, res) {
   var { selectedRows ,storage} = req.body
   
   var sqlstr = `
   --采购管理
update julongSRM_cgdd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --采购订单明细
update julongSRM_sltzd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --收料通知单明细
update julongSRM_cgrk_detail set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --采购入库明细
update julongSRM_qgd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --请购单明细
update julongSRM_cght_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --采购合同明细
update julongSRM_cgck_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --采购出库明细
--销售管理
update julongSRM_xsdd_mx set cpmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where cpbm = '${selectedRows[0].wlbm}' and cpmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh} ' --销售订单明细
update julongSRM_fhtzd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --发货通知单明细
update julongSRM_xsckd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --销售出库明细
update julongSRM_xsthd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --销售退货单明细
--工艺管理
update julongSRM_wlqd_mx set cpmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where cpbm = '${selectedRows[0].wlbm}' and cpmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}' --物料清单明细
update julongSRM_wlqd set cpmc='${selectedRows[0].wlmc}' where cpbm = '${selectedRows[0].wlbm}' and cpmc != '${selectedRows[0].wlmc}'  --物料清单
--财物管理
update julongSRM_xsfp_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --销售发票明细
--生产管理
update julongSRM_bgd set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --报工单
update julongSRM_scjh_mx set cpmc='${selectedRows[0].wlmc}' where wlbm = '${selectedRows[0].wlbm}' and cpmc != '${selectedRows[0].wlmc}'  --生产计划明细
update julongSRM_sclld_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --生产领料单明细
update julongSRM_scrkd_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --生产入库单明细
--库存管理
update julongSRM_dbd_mx set wlmc='${selectedRows[0].wlmc}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --调拨单明细
update julongSRM_qtrk_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --其他入库明细 
update julongSRM_qtck_mx set wlmc='${selectedRows[0].wlmc}',ggxh='${selectedRows[0].ggxh}' where wlbm = '${selectedRows[0].wlbm}' and (wlmc != '${selectedRows[0].wlmc}' or ggxh != '${selectedRows[0].ggxh}') --其他出库明细
   `
   logger.info(sqlstr)
   await saveczlog_util(sqlstr, storage, '', '更新历史单据物料名称', '更新', '')
   var rtdata = await usequery(sqlstr)
   res_util(res, {}, true, "更新成功")
});


//popview 
router.post('/getpopupview', async function (req, res) {
   var db = new Msdb();
   var viewname = req.body.viewname;
   var checkifviewres = await db.query(`SELECT * FROM sys.views WHERE name = '${viewname}' `)
   if (checkifviewres.recordsets[0].length == 0) {
      res_util(res, [], false, "不是视图查询失败")
   }
   else {
      var rtdata = await db.query(`select  TOP 1000 *  from ${viewname}`)
      res_util(res, rtdata.recordsets[0], true, "查询成功！")
   }

});

//用户导入 excel

router.post('/ry_import', async function (req, res) {
   var data=req.body

   var db=new Msdb()
  for(var i=0;i<data.length;i++){

      
  await db.update(`insert into julongSRM_ryda (id,create_time,create_by,xm,yhlx,dh,zh,mm,js,ryzt)

    values(NEXT VALUE FOR SEQ_ID,getdate(),'admin','${data[i].xm}','${data[i].yhlx}','${data[i].dh}','${data[i].zh}','${data[i].mm}',
    '${data[i].js}','正常')`)
  }

  await db.update(`merge into julongsrm_ryda a using julongsrm_js b on a.js=b.jsmc and a.jsid is null
  when matched then update set a.jsid=b.id;`)



   res_util(res, {}, true, "导入完成")
})


//查询销售订单明细通过料号
router.post('/query_crkmx_by_wlbm', async function (req, res) {
   var {selectedRows} = req.body
   var rtdata = await usequery(`select * from v_crkmx_history where wlbm in (${selectedRows.map(item => `'${item.wlbm}'`).join(',')})`)
   res_util(res, rtdata, true, "查询成功！")
});



//查询部门树
router.post('/tree_manage_bm', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_ss_bmda order by bmbm asc`)
   res_util(res, rtdata, true, "查询成功")
})

//新增部门
router.post('/tree_manage_addbm', async function (req, res) {
   var {bmmc,bmbm} = req.body
   // 生成新的部门编码
   let newBmbm = ''
   
   // 获取所有部门数据
   let allDepts = await usequery(`select bmbm from julongSRM_ss_bmda order by bmbm asc`)
   
   if(bmbm === '0') {
     // 父级是总部,查找0-x格式的最大编号
     let maxNum = 0
     allDepts.forEach(dept => {
       if(dept.bmbm.match(/^0-\d+$/)) {
         let num = parseInt(dept.bmbm.split('-')[1])
         maxNum = Math.max(maxNum, num)
       }
     })
     newBmbm = `0-${maxNum + 1}`
   } else {
     // 父级是其他部门,查找parentBmbm-x格式的最大编号
     let maxNum = 0
     allDepts.forEach(dept => {
       if(dept.bmbm.startsWith(bmbm + '-')) {
         let num = parseInt(dept.bmbm.split('-').pop())
         maxNum = Math.max(maxNum, num)
       }
     })
     newBmbm = `${bmbm}-${maxNum + 1}`
   }
   
   // 更新请求参数
   bmbm = newBmbm
   var rtdata = await usequery(`insert into julongSRM_ss_bmda (id,bmbm,bmmc) values (NEXT VALUE FOR SEQ_ID,'${bmbm}','${bmmc}')`)
   res_util(res, rtdata, true, "新增成功")
})

//查询物料分类new树
router.post('/wlfllb_new', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_wlfl_new order by flbm desc`)
   res_util(res, rtdata, true, "查询成功")
})



//查询物料分类new列表通过id
router.post('/query_wlfl_byid_new', async function (req, res) {
   var data = await usequery(`select * from julongSRM_wlfl_new where id='${req.body.id}' order by flbm desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})


//新增物料分类new
router.post('/add_wlfl_new', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from julongSRM_wlfl_new where flbm='${formdata.flbm.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，分类编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_wlfl_new")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增物料分类new', '新增', 'julongSRM_wlfl_new')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})



//更新物料分类new
router.post('/update_wlfl_new', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from julongSRM_wlfl_new where 
   flbm='${formdata.flbm.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，分类编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_wlfl_new")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新物料分类new', '更新', 'julongSRM_wlfl_new')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除物料分类new
router.post('/delete_wlfl_new', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_wlfl_new where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除物料分类new', '删除', 'julongSRM_wlfl_new')
   res_util(res, {}, true, "删除成功")
})


//查询物料档案列表
router.post('/wldalb', async function (req, res) {
   var rtdata = await usequery(`select *,lbbm+wlbm as wlbmpj from julongSRM_wlda order by wlbm desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询物料档案列表通过id
router.post('/query_wlda_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_wlda where id='${req.body.id}' order by wlbm desc`)
   var mxdata = await usequery(`select * from julongSRM_wlda_mx where mainid='${req.body.id}' order by bbh desc`)
   res_util(res, { formdata: data[0],mxdata}, true, "查询成功")
})


//新增物料档案
router.post('/add_wlda', async function (req, res) {
   let { formdata, storage,mxdata} = req.body
//    var ifwlbm = await checkbysql(` select top 1 * from julongSRM_wlda where wlbm='${formdata.wlbm.value}'`)
//    if (ifwlbm) {
//       res_util(res, { formdata }, false, "新增失败，物料编码已存在")
//       return
//    }
//    //不存在编码插入
//   let getnextwlbmsql = await usequery(`select top 1 wlbm from julongSRM_wlda where wlbm like '%${formdata.lbbm.value}%' order by wlbm desc`)
 
//  console.log("自动料号",getnextwlbmsql)
//   let nextwlbm = getnextwlbmsql.length > 0 ? parseInt(getnextwlbmsql[0].wlbm.slice(-4)) + 1 : 1
//   formdata.wlbm.value = formdata.lbbm.value + nextwlbm.toString().padStart(4, '0')


   var insrtobj = await insert_table(formdata, "julongSRM_wlda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增物料档案', '新增', 'julongSRM_wlda')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "julongSRM_wlda_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增物料档案明细', '新增', 'julongSRM_wlda_mx')                     
   }
   var rtmxdata = await usequery(`select  *  from julongSRM_wlda_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: insrtobj.data,mxdata:rtmxdata}, true, "新增成功")
})




//更新物料档案
router.post('/update_wlda', async function (req, res) {
   let { formdata, storage,mxdata,delidarr} = req.body
   var db=new Msdb()
   var ifwlbm = await checkbysql(` select top 1 * from julongSRM_wlda where 
   wlbm='${formdata.wlbm.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，物料编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_wlda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新物料档案', '更新', 'julongSRM_wlda')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from julongSRM_wlda_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "julongSRM_wlda_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "julongSRM_wlda_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from julongSRM_wlda_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from julongSRM_wlda_mx where mainid ='${formdata.id.value}'`)


   var sqlstr = `
   --采购管理
update julongSRM_cgdd_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --采购订单明细
update julongSRM_sltzd_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --收料通知单明细
update julongSRM_cgrk_detail set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --采购入库明细
update julongSRM_qgd_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --请购单明细
update julongSRM_cght_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --采购合同明细
update julongSRM_cgck_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --采购出库明细
--销售管理
update julongSRM_xsdd_mx set cpmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where cpbm = '${formdata.wlbm.value}' and (cpmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --销售订单明细
update julongSRM_fhtzd_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --发货通知单明细
update julongSRM_xsckd_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --销售出库明细
update julongSRM_xsthd_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --销售退货单明细
--工艺管理
update julongSRM_wlqd_mx set cpmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where cpbm = '${formdata.wlbm.value}' and (cpmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --物料清单明细
update julongSRM_wlqd set cpmc='${formdata.wlmc.value}' where cpbm = '${formdata.wlbm.value}' and (cpmc != '${formdata.wlmc.value}') --物料清单
--财物管理
update julongSRM_xsfp_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --销售发票明细
--生产管理
update julongSRM_bgd set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --报工单
update julongSRM_scjh_mx set cpmc='${formdata.wlmc.value}' where wlbm = '${formdata.wlbm.value}' and (cpmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --生产计划明细
update julongSRM_sclld_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' ) --生产领料单明细
update julongSRM_scrkd_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --生产入库单明细
--库存管理
update julongSRM_dbd_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --调拨单明细
update julongSRM_qtrk_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --其他入库明细 
update julongSRM_qtck_mx set wlmc='${formdata.wlmc.value}', ggxh='${formdata.ggxh.value}' where wlbm = '${formdata.wlbm.value}' and (wlmc != '${formdata.wlmc.value}' or ggxh != '${formdata.ggxh.value}') --其他出库明细
   `
   await db.update(sqlstr)
   res_util(res, { formdata: updatobj.data,mxdata:rtmxdata}, true, "更新成功")
})

//删除物料档案
router.post('/delete_wlda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_wlda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from julongSRM_wlda_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除物料档案', '删除', 'julongSRM_wlda')
   res_util(res, {}, true, "删除成功")
})

//启用物料档案
router.post('/enable_wlda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   for(let i=0; i<selectedRows.length;i++){
      const rtdata = await usequery(`select * from julongSRM_wlda where id='${selectedRows[i].id}'`)
      var sqlupdate=`update julongSRM_wlda set sfqy='已启用' where id='${selectedRows[i].id}'`
      await db.update(sqlupdate)
      await saveczlog_util(sqlupdate, storage, '', '启用物料档案', '修改', 'julongSRM_wlda')
   }
   res_util(res, {}, true, "修改成功")
})

//禁用物料档案
router.post('/disable_wlda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   for(let i=0; i<selectedRows.length;i++){
      const rtdata = await usequery(`select * from julongSRM_wlda where id='${selectedRows[i].id}'`)
      var sqlupdate=''

      sqlupdate=`update julongSRM_wlda set sfqy='已禁用' where id='${selectedRows[i].id}'`

      await db.update(sqlupdate)
      await saveczlog_util(sqlupdate, storage, '', '禁用物料档案', '修改', 'julongSRM_wlda')
   }
   res_util(res, {}, true, "修改成功")
})


//查询供应商档案列表
router.post('/gyslb', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_gys where hzzt = '正式合作' order by gsmc desc`)
   var bxrtdata = await usequery(`select * from julongSRM_gys where hzzt = '备选'  order by gsmc desc`)
   var hmdrtdata = await usequery(`select * from julongSRM_gys where hzzt = '黑名单' order by gsmc desc`)
   res_util(res, {rtdata,bxrtdata,hmdrtdata}, true, "查询成功")
})

//查询供应商档案列表通过id
router.post('/query_gys_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_gys where id='${req.body.id}'`)
   var ghmxdata = await usequery(`select b.* from julongSRM_gys a,julongSRM_gys_ghmx b where a.id=b.mainid and a.id='${data[0].id}'`)
   var sqmxdata = await usequery(`select b.* from julongSRM_gys a,julongSRM_gys_sqmx b where a.id=b.mainid and a.id='${data[0].id}'`)
   var yhmxdata = await usequery(`select b.* from julongSRM_gys a,julongSRM_gys_yhmx b where a.id=b.mainid and a.id='${data[0].id}'`)
   var zzmxdata = await usequery(`select b.* from julongSRM_gys a,julongSRM_gys_zsmx b where a.id=b.mainid and a.id='${data[0].id}'`)
   var kpmxdata = await usequery(`select b.* from julongSRM_gys a,julongSRM_gys_kpmx b where a.id=b.mainid and a.id='${data[0].id}'`)

   res_util(res, {formdata:data[0],ghmxdata,sqmxdata,yhmxdata,zzmxdata,kpmxdata}, true, "查询成功")
})

//新增供应商档案
router.post('/add_gys', async function (req, res) {
   let { formdata,ghmxdata,sqmxdata,yhmxdata,zzmxdata,kpmxdata,storage } = req.body
   // var ifwlbm = await checkbysql(` select top 1 * from julongSRM_gys where gysdm='${formdata.gysdm.value}'`)
   // if (ifwlbm) {
   //    res_util(res, { formdata }, false, "新增失败，供应商编码已存在")
   //    return
   // }
   var gysdmData = await usequery(` select top 1 * from julongSRM_gys order by gysdm desc `)
   var nextgysdm = gysdmData.length > 0 ? parseInt(gysdmData[0].gysdm.slice(-5)) + 1 : '10000'
   formdata.gysdm.value = 'GYS' + nextgysdm.toString().padStart(4, '0')
   //不存在编码插入

   var insrtobj = await insert_table(formdata, "julongSRM_gys")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增供应商档案', '新增', 'julongSRM_gys')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in ghmxdata) {
      let mxitem = ghmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "julongSRM_gys_ghmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商供货明细', '新增', 'julongSRM_gys_ghmx')                     
   }
   for (var mxkey in sqmxdata) {
      let mxitem = sqmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "julongSRM_gys_sqmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商授权明细', '新增', 'julongSRM_gys_sqmx')                     
   }
   for (var mxkey in yhmxdata) {
      let mxitem = yhmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "julongSRM_gys_yhmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商银行明细', '新增', 'julongSRM_gys_yhmx')                     
   }
   for (var mxkey in zzmxdata) {
      let mxitem = zzmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "julongSRM_gys_zsmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商资质明细', '新增', 'julongSRM_gys_zsmx')                     
   }
   for (var mxkey in kpmxdata) {
      let mxitem = kpmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "julongSRM_gys_kpmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增供应商考评明细', '新增', 'julongSRM_gys_kpmx')                     
   }

   var rtghmxdata = await usequery(`select *  from julongSRM_gys_ghmx where mainid ='${formdata.id.value}'`)
   var rtsqmxdata = await usequery(`select *  from julongSRM_gys_sqmx where mainid ='${formdata.id.value}'`)
   var rtyhmxdata = await usequery(`select *  from julongSRM_gys_yhmx where mainid ='${formdata.id.value}'`)
   var rtzzmxdata = await usequery(`select *  from julongSRM_gys_zsmx where mainid ='${formdata.id.value}'`)
   var rtkpmxdata = await usequery(`select *  from julongSRM_gys_kpmx where mainid ='${formdata.id.value}'`)

   res_util(res, { formdata: formdata,ghmxdata:rtghmxdata,sqmxdata:rtsqmxdata,yhmxdata:rtyhmxdata,zzmxdata:rtzzmxdata,kpmxdata:rtkpmxdata }, true, "新增成功")
})

//更新供应商档案
router.post('/update_gys', async function (req, res) {
   let { formdata,ghmxdata,sqmxdata,yhmxdata,zzmxdata,ghdelidarr,sqdelidarr,yhdelidarr,zzdelidarr, storage } = req.body
   // var ifwlbm = await checkbysql(` select top 1 * from julongSRM_gys where 
   // bm='${formdata.bm.value}' and id !='${formdata.id.value}'`)
   // if (ifwlbm) {
   //    res_util(res, { formdata }, false, "更新失败，物料编码已存在")
   //    return
   // }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_gys")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新供应商档案', '更新', 'julongSRM_gys')

   var mainid = updatobj.data.id.value;
   for (var mxkey in ghmxdata) {
      let mxitem = ghmxdata[mxkey];
      var ifcz = await checkbysql(`select * from julongSRM_gys_ghmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "julongSRM_gys_ghmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "julongSRM_gys_ghmx")
      }
   }
   for(var mxkey in sqmxdata){
      let mxitem = sqmxdata[mxkey];
      var ifcz = await checkbysql(`select * from julongSRM_gys_sqmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "julongSRM_gys_sqmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "julongSRM_gys_sqmx")
      }
   }
   for(var mxkey in yhmxdata){
      let mxitem = yhmxdata[mxkey];
      var ifcz = await checkbysql(`select * from julongSRM_gys_yhmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "julongSRM_gys_yhmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "julongSRM_gys_yhmx")
      }
   }
   for(var mxkey in zzmxdata){
      let mxitem = zzmxdata[mxkey];
      var ifcz = await checkbysql(`select * from julongSRM_gys_zsmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "julongSRM_gys_zsmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "julongSRM_gys_zsmx")
      }
   }
   
   //删除delidarr
   await db.update(`delete from julongSRM_gys_ghmx where id in (${ghdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from julongSRM_gys_sqmx where id in (${sqdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from julongSRM_gys_yhmx where id in (${yhdelidarr.map(item => `'${item}'`).join(',')}) `)
   await db.update(`delete from julongSRM_gys_zsmx where id in (${zzdelidarr.map(item => `'${item}'`).join(',')}) `)

   var rtghmxdata = await usequery(`select *  from julongSRM_gys_ghmx where mainid ='${formdata.id.value}'`)
   var rtsqmxdata = await usequery(`select *  from julongSRM_gys_sqmx where mainid ='${formdata.id.value}'`)
   var rtyhmxdata = await usequery(`select *  from julongSRM_gys_yhmx where mainid ='${formdata.id.value}'`)
   var rtzzmxdata = await usequery(`select *  from julongSRM_gys_zsmx where mainid ='${formdata.id.value}'`)
   var rtkpmxdata = await usequery(`select *  from julongSRM_gys_kpmx where mainid ='${formdata.id.value}'`)

   
   res_util(res, { formdata: updatobj.data,ghmxdata:rtghmxdata,sqmxdata:rtsqmxdata,yhmxdata:rtyhmxdata,zzmxdata:rtzzmxdata,kpmxdata:rtkpmxdata }, true, "更新成功")
})

//删除供应商档案
router.post('/delete_gys', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_gys where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除供应商档案', '删除', 'julongSRM_wlda')
   res_util(res, {}, true, "删除成功")
})



//查询注册申请列表
router.post('/zcxxlb', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_zcb order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//新增车间档案
router.post('/add_zcxx', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_zcb where dh='${formdata.dh.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，已经存在申请记录")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_zcb")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增注册申请', '新增', 'julongSRM_zcb')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//查询注册申请列表通过id
router.post('/query_zcxx_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_zcb where id='${req.body.id}' order by create_time desc`)
   res_util(res, {formdata:data[0]}, true, "查询成功")
})


//新增车间档案
router.post('/update_zcxx', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_zcb where dh='${formdata.dh.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，已经存在更新记录")
      return
   }
   
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_zcb")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新注册信息', '更新', 'julongSRM_zcb')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})


//驳回注册申请
router.post('/refuse_zcxx', async function (req, res) {
   let { formdata, storage } = req.body
   formdata.clzt.value  = "已驳回"
   let updatobj = await update_table_byid(formdata, "julongSRM_zcb")
   //发送驳回消息的短信并调用短信平台（Msg = “您的申请已被驳回，请重新申请”）
//    const config = {
//       credentials: {

//         //aksk 需要在申请后进行替换
//           ak: 'ALTAKeJlSZgJJDpTGyKbvPCiSw',
//           sk: '182e0fa511b944dfa4b5763fa989e3e2'
//       },
//       endpoint: 'http://smsv3.bj.baidubce.com'
//   };

//       let fhxx = ''
//       let fhid = ''
//      //发送短信开始  启动云服务客户端
//       let client = new sdk.HttpClient(config);
      
//       //拼接多个请求字节头
//       let path = "/api/v3/sendSms";
//       let body = JSON.stringify({
//           "mobile": formdata.dh.value,
//           //短信签名需要替换为拒绝签名
//           "signatureId": "sms-sign-SRNNYU81891",
//           //短信模板需要替换为拒绝模板
//           "template": "sms-tmpl-axaHQQ35906",

//           //入参变量
//           "contentVar": {
//               "xm": formdata.gsmc.value,
//           }
//       })
//       let params = {"clientToken": ""};
//       let headers = {"Content-Type": "application/json", "Accept": "application/json"};

//      //发送请求 客户端调度命令
//       await client.sendRequest("POST", path, body, headers, params)
//           .then(function (response) {
//               console.log("object: ", response.body)
//               fhxx = response.body.message
//               fhid = response.body.requestId
//           }, function (error) {
//               console.log("error: ", error)
//           });

//           formdata.fhxx.value = fhxx
//           formdata.fhid.value = fhid
          
          await update_table_byid(formdata, "julongSRM_zcb")

   await saveczlog_util(updatobj.updatesql, storage, '', '驳回注册申请', '修改', 'julongSRM_zcb')

   res_util(res, { formdata }, true, "驳回成功")
})

//通过并创建账号
router.post('/pass_zcxx', async function (req, res) {
   let { formdata,ryformdata,gysformdata, storage } = req.body
   formdata.clzt.value  = "已通过"
   console.log("formdata================",formdata)
   // 生成6位随机密码
   function generateRandomPassword() {
      let password = '';
      for(let i = 0; i < 6; i++) {
         password += Math.floor(Math.random() * 10);
      }
      return password;
   }
   //修改注册申请状态
   

   //发送通过消息的短信并调用短信平台（Msg = “您的申请已通过，请使用账号和密码登录系统，系统地址为”）
  
      // const config = {
      //     credentials: {

      //       //aksk 需要在申请后进行替换
      //         ak: 'ALTAKeJlSZgJJDpTGyKbvPCiSw',
      //         sk: '182e0fa511b944dfa4b5763fa989e3e2'
      //     },
      //     endpoint: 'http://smsv3.bj.baidubce.com'
      // };

      var gysdmData = await usequery(` select top 1 * from julongSRM_gys order by gysdm desc `)
      var nextgysdm = gysdmData.length > 0 ? parseInt(gysdmData[0].gysdm.slice(-5)) + 1 : '10000'
      var gysdm = 'GYS' + nextgysdm.toString().padStart(5, '0')
   

      ryformdata.zh.value = gysdm
      ryformdata.mm.value = generateRandomPassword();
      ryformdata.ryzt.value = "正常"
      ryformdata.yhlx = '租户用户'
      ryformdata.dh = formdata.dh.value
      ryformdata.xm.value = formdata.gsmc.value
      ryformdata.js.value = "供应商【临时】"
      ryformdata.glgys.value = formdata.gsmc.value
      ryformdata.jsid.value = 1000868
      
   
      gysformdata.gsmc.value = formdata.gsmc.value
      gysformdata.hzzt.value = "准入中"
      gysformdata.xysbdm.value = formdata.xysbdm.value
      gysformdata.gysdm.value = gysdm
      

          let fhxx = ''
          let fhid = ''
         //发送短信开始  启动云服务客户端
         //  let client = new sdk.HttpClient(config);
          
         //  //拼接多个请求字节头
         //  let path = "/api/v3/sendSms";
         //  let body = JSON.stringify({
         //      "mobile": formdata.dh.value,
         //      //短信签名需要替换
         //      "signatureId": "sms-sign-SRNNYU81891",
         //      //短信模板需要替换
         //      "template": "sms-tmpl-axaHQQ35906",

         //      //入参变量
         //      "contentVar": {
         //          "xm": formdata.gsmc.value,
         //          "zh": ryformdata.zh.value,
         //          "mm": ryformdata.mm.value,
         //          "fwdz": "http://192.168.1.100:8080/julongSRM/login"
         //      }
         //  })
         //  let params = {"clientToken": ""};
         //  let headers = {"Content-Type": "application/json", "Accept": "application/json"};

         // //发送请求 客户端调度命令
         //  await client.sendRequest("POST", path, body, headers, params)
         //      .then(function (response) {
         //          console.log("object: ", response.body)
         //          fhxx = response.body.message
         //          fhid = response.body.requestId
         //      }, function (error) {
         //          console.log("error: ", error)
         //      });

         //      formdata.fhxx.value = fhxx
         //      formdata.fhid.value = fhid
              await update_table_byid(formdata, "julongSRM_zcb")
  
   //发送短信结束
 
   
  

   

   var insrtobj = await insert_table(ryformdata, "julongSRM_ryda")
   var insrtobj2 = await insert_table(gysformdata, "julongSRM_gys")
   await saveczlog_util(insrtobj.insertsql, storage, '', '注册申请审批通过并创建账号', '新增', 'julongSRM_ryda')
   await saveczlog_util(insrtobj2.insertsql, storage, '', '注册申请审批通过并创建账号', '新增', 'julongSRM_gys')

   

   res_util(res, { formdata }, true, "注册申请受理通过并创建账号成功")
})


//查询人员档案列表
router.post('/rylb', async function (req, res) {
   var rtdata = await usequery(`select *,
      ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) as bmmc_row,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) = 1 
         THEN bmmc ELSE NULL END AS bmmcq,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY bmmc ORDER BY bmmc) = 1 
         THEN gw ELSE NULL END AS gwq
      from julongSRM_ryda where ryzt='${req.body.type}' order by bmmc,gw desc `)
   res_util(res, rtdata, true, "查询成功")
})

//查询人员档案列表通过id
router.post('/query_ry_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_ryda where id='${req.body.id}' order by rzsj desc`)
   res_util(res, {formdata:data[0]}, true, "查询成功")
})

//新增人员档案
router.post('/add_ry', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from julongSRM_ryda where zh='${formdata.zh.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，账号已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_ryda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增人员档案', '新增', 'julongSRM_ryda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新人员档案
router.post('/update_ry', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from julongSRM_ryda where 
   zh='${formdata.zh.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，账号已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_ryda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新人员档案', '更新', 'julongSRM_ryda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})


//删除人员档案
router.post('/delete_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_ryda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除人员档案', '删除', 'julongSRM_ryda')
   res_util(res, {}, true, "删除成功")
})

//禁用人员档案
router.post('/disable_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `update julongSRM_ryda set ryzt='禁用',lzsj=getdate() where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '禁用人员档案', '修改', 'julongSRM_ryda')
   res_util(res, {}, true, "禁用成功")
})

//启用人员档案
router.post('/able_ry', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `update julongSRM_ryda set ryzt='正常',rzsj=getdate() where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '启用人员档案', '修改', 'julongSRM_ryda')
   res_util(res, {}, true, "启用成功")
})

//查询部门档案列表
router.post('/bmlb', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_bmda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询部门档案列表通过id
router.post('/query_bm_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_bmda where id='${req.body.id}' order by create_time desc`)
   res_util(res, {formdata:data[0]}, true, "查询成功")
})

//新增部门档案
router.post('/add_bm', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from julongSRM_bmda where bmbh='${formdata.bmbh.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "新增失败，工号已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_bmda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增部门档案', '新增', 'julongSRM_bmda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新部门档案
router.post('/update_bm', async function (req, res) {
   let { formdata, storage } = req.body
   var ifwlbm = await checkbysql(` select top 1 * from julongSRM_bmda where 
   bmbh='${formdata.bmbh.value}' and id !='${formdata.id.value}'`)
   if (ifwlbm) {
      res_util(res, { formdata }, false, "更新失败，部门编号已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_bmda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新部门档案', '更新', 'julongSRM_bmda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除部门档案
router.post('/delete_bm', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_bmda where id = '${selectedRows.id}'`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除部门档案', '删除', 'julongSRM_wlda')
   res_util(res, {}, true, "删除成功")
})


//导入客户信息 excel

router.post('/khda_import', async function (req, res) {
   var data=req.body
   var db=new Msdb()
  for(var i=0;i<data.length;i++){

      
  await db.update(`insert into julongSRM_khda(id,create_time,create_by,mc,lxr,dh,dz,bm)
    values(NEXT VALUE FOR SEQ_ID,getdate(),'admin','${data[i].mc}','${data[i].lxr}','${data[i].dh}','${data[i].dz}','${data[i].bm}')`)
   
  }


   res_util(res, {}, true, "导入完成")
})

//查询客户档案列表
router.post('/khdalb', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_khda order by bm desc`)
   res_util(res, rtdata, true, "查询成功")
})


//查询客户档案列表通过id
router.post('/query_khda_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_khda where id='${req.body.id}' order by bm desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增客户档案
router.post('/add_khda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_khda where bm='${formdata.bm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，客户编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_khda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增客户档案', '新增', 'julongSRM_khda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新客户档案
router.post('/update_khda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_khda where 
   bm='${formdata.bm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，客户编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_khda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新客户档案', '更新', 'julongSRM_khda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除客户档案
router.post('/delete_khda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_khda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除客户档案', '删除', 'julongSRM_khda')
   res_util(res, {}, true, "删除成功")
})

//查询车间档案列表
router.post('/cjdalb', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_cjda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询车间档案列表通过id
router.post('/query_cjda_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_cjda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增车间档案
router.post('/add_cjda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_cjda where cjbm='${formdata.cjbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，车间编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_cjda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增车间档案', '新增', 'julongSRM_cjda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新车间档案
router.post('/update_cjda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_cjda where 
   cjbm='${formdata.cjbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，车间编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_cjda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新车间档案', '更新', 'julongSRM_cjda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除车间档案
router.post('/delete_cjda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_cjda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除车间档案', '删除', 'julongSRM_cjda')
   res_util(res, {}, true, "删除成功")
})


//查询物料分类树
router.post('/wlfllb', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_wlfl order by flbm asc`)
   res_util(res, rtdata, true, "查询成功")
})


//查询物料分类列表通过id
router.post('/query_wlfl_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_wlfl where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增物料分类
router.post('/add_wlfl', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_wlfl where flbm='${formdata.flbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，物料分类编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_wlfl")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增物料分类', '新增', 'julongSRM_wlfl')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//新增一级分类
router.post('/add_wlfl_yjfl', async function (req, res) {
   let { flbm,flmc, storage } = req.body
   let db=new Msdb()
   var ifbm = await checkbysql(` select top 1 * from julongSRM_wlfl where flbm='${flbm}'`)
   if (ifbm) {
      res_util(res, { }, false, "新增失败，物料分类编码已存在")
      return
   }
   //不存在编码插入
   let id=await getxl_util()
   let sql=`insert into julongSRM_wlfl(id,create_time,create_by,flbm,flmc,sjflid) values('${id}',getdate(),'${storage.ygxm}','${flbm}','${flmc}','000')`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '新增物料分类', '新增', 'julongSRM_wlfl')
   res_util(res, {}, true, "新增成功")
})

//新增二级分类
router.post('/add_wlfl_zjfl', async function (req, res) {
   let { flbm,flmc,sjflbm,sjflmc, storage } = req.body
   let db=new Msdb()
   var ifbm = await checkbysql(` select top 1 * from julongSRM_wlfl where flbm='${flbm}'`)
   if (ifbm) {
         res_util(res, { }, false, "新增失败，物料分类编码已存在")
      return
   }
   var sjfldata=await usequery(`select id from julongSRM_wlfl where flbm='${sjflbm}'`)
   //不存在编码插入
   let id=await getxl_util()
   let sql=`insert into julongSRM_wlfl(id,create_time,create_by,flbm,flmc,sjflid,sjflbm,sjflmc) values('${id}',getdate(),'${storage.ygxm}','${flbm}','${flmc}','${sjfldata[0].id}','${sjflbm}','${sjflmc}')`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '新增物料分类', '新增', 'julongSRM_wlfl')
   
   // //查询一级分类是不是一级分类
   // var ejflList=await usequery(`select * from julongSRM_wlfl where yjid='${sjflbm}'`)
   // //查询一级分类是不是一级分类
   // var sjflList=await usequery(`select * from julongSRM_wlfl where ejid='${sjflbm}'`)
   // if(ejflList.length==0){
   //    let id2=await getxl_util()
   //    let ejflbm=ejflList.length>0?ejflList.length.padStart(2,'0'):'01'
   //    let mc=sjflmc+flmc
   //    let sql2=`insert into julongSRM_wlfl_new(id,create_time,create_by,flbm,flmc,yjflmc,yjflbm,ejflmc,ejflbm) 
   //    values('${id2}',getdate(),'${storage.ygxm}','${sjflmc}','${sjflbm}','${mc}','${sjflbm}','${flmc}','${ejflbm}')`
   //    await db.update(sql2)
   // }
   // else{
   //    let id2=await getxl_util()
   //    let ejflbm=ejflList.length>0?ejflList.length.padStart(2,'0'):'01'
   //    let sjflbm=sjflList.length>0?sjflList.length.padStart(2,'0'):'01'
   //    let mc=sjflmc+flmc
   //    let sql2=`insert into julongSRM_wlfl_new(id,create_time,create_by,flbm,flmc,yjflmc,yjflbm,ejflmc,ejflbm,sjflmc,sjflbm) 
   //    values('${id2}',getdate(),'${storage.ygxm}','${sjflmc}','${sjflbm}','${mc}','${sjflbm}','${flmc}','${ejflbm}','${sjflmc}','${sjflbm}')`
   //    await db.update(sql2)
   // }
   res_util(res, {}, true, "新增成功")
})

//更新物料分类
router.post('/update_wlfl', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_wlfl where 
   flbm='${formdata.flbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，物料分类编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_wlfl")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新物料分类', '更新', 'julongSRM_wlfl')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除物料分类
router.post('/delete_wlfl', async function (req, res) {
   let { selectedKeys, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_wlfl where flbm='${selectedKeys}'`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除物料分类', '删除', 'julongSRM_wlfl')
   res_util(res, {}, true, "删除成功")
})

//查询产线档案列表
router.post('/cxdalb', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_cxda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询产线档案列表通过id
router.post('/query_cxda_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_cxda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增产线档案
router.post('/add_cxda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_cxda where cxbm='${formdata.cxbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，产线编码已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_cxda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增产线档案', '新增', 'julongSRM_cxda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新产线档案
router.post('/update_cxda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_cxda where 
   cxbm='${formdata.cxbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，产线编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_cxda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新产线档案', '更新', 'julongSRM_cxda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除产线档案
router.post('/delete_cxda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_cxda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除产线档案', '删除', 'julongSRM_cxda')
   res_util(res, {}, true, "删除成功")
})


//查询仓库档案列表
router.post('/ckdalb', async function (req, res) {
   var rtdata = await usequery(`select * from julongSRM_ckda order by ckbm desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询仓库档案列表通过id
router.post('/query_ckda_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_ckda where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增仓库档案
router.post('/add_ckda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_ckda where ckbm='${formdata.ckbm.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "新增失败，仓库档案已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "julongSRM_ckda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增仓库档案', '新增', 'julongSRM_ckda')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新仓库档案
router.post('/update_ckda', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from julongSRM_ckda where 
   ckbm='${formdata.ckbm.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，物料编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "julongSRM_ckda")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新仓库档案', '更新', 'julongSRM_ckda')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除仓库档案
router.post('/delete_ckda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_ckda where id = '${selectedRows.id}'`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除仓库档案', '新增', 'julongSRM_ckda')
   res_util(res, {}, true, "删除成功")
})



//查询角色数据
router.post('/queryJsDataSource',async function(req,res){
      var rtdata=await usequery(`select * from julongSRM_js`);
      res_util(res, rtdata, true, "查询成功")
   });

//查询物料数据
router.post('/queryWlmcDataSource',async function(req,res){
   var rtdata=await usequery(`select * from julongSRM_wlda`);
   res_util(res, rtdata, true, "查询成功")
});

//查询仓库数据
router.post('/queryCkDataSource',async function(req,res){
   var rtdata=await usequery(`select *  from julongSRM_ckda`);
   res_util(res, rtdata, true, "查询成功")
});

//查询产品档案列表
router.post('/cpdalb', async function (req, res) {
   let {storage } = req.body
   var rtdata = await usequery(`select * from julongSRM_cpda order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询产品档案列表通过id
router.post('/query_cpda_byid', async function (req, res) {
   var data = await usequery(`select * from julongSRM_cpda where id='${req.body.data.id}' order by create_time desc`)
   var mxdata = await usequery(`select b.* from julongSRM_wlqd a,julongSRM_wlqd_mx b where a.id=b.mainid and a.cpbm='${data[0].wlbm}'`)
   var gylxmxdata = await usequery(`select b.* from julongSRM_gylx a,julongSRM_gylx_gymx b where a.id=b.mainid and a.cpbm='${data[0].wlbm}' order by b.sx asc`)
   res_util(res, { formdata: data[0],mxdata,gylxmxdata}, true, "查询成功")
})

//新增产品档案
router.post('/add_cpda', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   var insrtobj = await insert_table(formdata, "julongSRM_cpda")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增产品档案', '新增', 'julongSRM_cpda')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "julongSRM_cpda_tzbgmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增产品档案明细', '新增', 'julongSRM_cpda_tzbgmx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from julongSRM_cpda_tzbgmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增产品档案成功`)
})

//更新产品档案
router.post('/update_cpda', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "julongSRM_cpda")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改产品档案', '修改', 'julongSRM_cpda')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from julongSRM_cpda_tzbgmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "julongSRM_cpda_tzbgmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "julongSRM_cpda_tzbgmx")
      }
   }
   
   //删除delidarr
   await db.update(`delete from julongSRM_cpda_tzbgmx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from julongSRM_cpda_tzbgmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新产品档案成功")
})

//  删除产品档案
router.post('/delete_cpda', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from julongSRM_cpda where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from julongSRM_cpda_tzbgmx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除产品档案', '删除', 'julongSRM_cpda')
   res_util(res, {}, true, "删除成功")
})


module.exports = router; 